﻿using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

using BLL.Services.Categories.Listing;

namespace BLL.Services.Categories
{
    public class CategoryCollection
    {

        private readonly string _connectionString;

        public CategoryCollection(string connectionString)
        {
            _connectionString = connectionString;
        }

        /// <summary>
        /// Gets the by id.
        /// </summary>
        /// <param name="categoryId">The category id.</param>
        public Category GetById(int categoryId)
        {
            Category result = null;
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Categories.SelectById(conn, categoryId);

                var authorParameter = new SqlParameter
                    {
                        ParameterName = "@categoryId",
                        Value = categoryId,
                        SqlDbType = SqlDbType.Int
                    };
                cmd.Parameters.Add(authorParameter);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                        result = GetCategory(reader);
                }
            }
            return result;
        }

        /// <summary>
        /// Creates the specified model.
        /// </summary>
        /// <param name="model">The model.</param>
        public void Create(Category model)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Categories.Insert(conn, model.Name);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (reader.HasRows)
                        {
                            model = GetCategory(reader);
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Saves the specified model.
        /// </summary>
        /// <param name="model">The model.</param>
        public void Save(Category model)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Categories.Update(conn, model.CategoryId, model.Name);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var category = GetCategory(reader);
                    }
                }
            }
        }

        /// <summary>
        /// Deletes the specified category id.
        /// </summary>
        /// <param name="categoryId">The category id.</param>
        public void Delete(int categoryId)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Categories.Delete(conn, categoryId);

                cmd.ExecuteNonQuery();
            }
        }



        /// <summary>
        /// Gets the category listing.
        /// </summary>
        /// <param name="page">The page.</param>
        /// <param name="size">The size.</param>
        /// <param name="column">The column.</param>
        /// <param name="direction">The direction.</param>
        /// <param name="filter">The filter.</param>
        public CategoryListing GetCategoryListing(
            int page, int size, string column, int direction, CategoryFilter filter)
        {
            if (filter == null)
                filter = new CategoryFilter();

            var result = new CategoryListing
                { Page = page, Size = size, Direction = direction, Column = column, Filter = filter };
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Categories.GetListing(conn, page, size, column, direction, filter.Name);

                var totalCount = new SqlParameter("@TotalCount", SqlDbType.Int, 4)
                    { Direction = ParameterDirection.ReturnValue };
                cmd.Parameters.Add(totalCount);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var item = new CategoryListing.Category();
                        item.CategoryId = reader.GetInt32(0);
                        item.Name = reader.GetString(1);
                        item.CountBooks = reader.GetInt32(2);
                        result.Categories.Add(item);
                    }
                }
                result.TotalPages = (int)totalCount.Value % size == 0
                                        ? (int)totalCount.Value / size
                                        : ((int)totalCount.Value / size) + 1;
            }
            return result;
        }

        /// <summary>
        /// Get category from data reader
        /// </summary>
        /// <param name="reader">The reader.</param>
        private static Category GetCategory(SqlDataReader reader)
        {
            var category = new Category();
            category.CategoryId = reader.GetInt32(0);
            category.Name = reader[1] as string;
            category.ParentId = reader.GetInt32(2);

            return category;
        }

        /// <summary>
        /// Selects all.
        /// </summary>
        public IEnumerable<Category> SelectAll()
        {
            var result = new List<Category>();
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Categories.SelectAll(conn);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var category = GetCategory(reader);
                        result.Add(category);
                    }
                }
            }
            return result;
        }

        public bool Validate(int categoryId, string name)
        {
            var result = false;
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Categories.Validate(conn, categoryId, name);

                var isValidParameter = new SqlParameter("@valid", SqlDbType.Bit, 4) 
                    { Direction = ParameterDirection.ReturnValue };
                cmd.Parameters.Add(isValidParameter);

                cmd.ExecuteNonQuery();
                result = (int)isValidParameter.Value == 1;
            }
            return result;
        }
    }
}
